package com.sai.transacciones;

import com.sai.clases.Alumno;
import com.sai.conexion.PoolConexion;
import com.sai.sql.SqlAlumno;
import com.sai.sql.SqlCalificacion;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class TAlumno {
    private static ConexionBD Conex;
    private String nombre, appat,apmat,matricula,grupo,proyecto,id;
     public TAlumno(String nom, String ap,String am, String m, String g, String p)
    {
        this.nombre = nom;
        this.appat = ap;
        this.apmat = am;
        this.matricula = m;
        this.grupo = g;
        this.proyecto = p;
        
    }
    
    public TAlumno(){
        nombre ="";
        appat ="";
        apmat="";
        matricula="";  
        grupo= "";
        proyecto = "";
        id="";
    }
        
    public static String Registrar(Alumno alumno) {
        Connection conex = (Connection) PoolConexion.getInstance().getConexion();
        String resp = null;

        try{
            PoolConexion.getInstance().beginTransaction(conex);
            
            PreparedStatement stm = null;
            
                stm = conex.prepareStatement(SqlAlumno.INSERT);
                stm.setString(1, alumno.getNombre());
                stm.setString(2, alumno.getApellidoM());
                stm.setString(3, alumno.getApellidoP());
                stm.setString(4, alumno.getMatricula());
                stm.setString(5, alumno.getIdGrupo());
                stm.setString(6, alumno.getNombreProyecto());
            
            stm.executeUpdate();

            PoolConexion.getInstance().commit(conex);
            PoolConexion.getInstance().endTransaction(conex);
            resp = "OK";
        } catch (SQLException ex) {
            return "Error al registrar el Alumno." + ex;
        } finally {
            PoolConexion.getInstance().cerrarConexion(conex);
        }
        return resp;
    }
    
    public static String Consultar() {
      
        Connection conex = (Connection) PoolConexion.getInstance().getConexion();
        String tablaAutores;

        try {
            PoolConexion.getInstance().commit(conex);
            PreparedStatement stm = null;
            ResultSet rs = null;

            stm = (PreparedStatement) conex.prepareStatement(SqlAlumno.SELECT_ALL);
            rs = stm.executeQuery();

            tablaAutores = "<table align='center' border='1' color='red'>"
                + "<tr>"
                + "<th>Nombre</th>"
                + "<th>Apellido Paterno</th>"
                + "<th>Apellido Materno</th>"
                + "<th>Matricula</th>"
                + "<th>Grupo</th>"
                + "<th>Proyecto</th>"
                + "</tr>";

            while (rs.next()) {
                tablaAutores += "<tr>"
                        + "<td>" + rs.getString(2) + "</td>"
                        + "<td>" + rs.getString(3) + "</td>"
                        + "<td>" + rs.getString(4) + "</td>"
                        + "<td>" + rs.getString(5) + "</td>"
                        + "<td>" + rs.getString(6) + "</td>"
                        + "<td>" + rs.getString(7) + "</td>"
                        + "<td>"+"<button type='button' name='btnActualizarr'"
                        + "onclick='busquedaAlumno("+rs.getInt(1)+");'>Actualizar</button></td>"
                        + "<td>"+"<button type='button' name='btnEliminar'"
                        + "onclick='borrarAlumno("+rs.getInt(1)+");'>Eliminar</button></td>"
                        + "</tr>";
                        
            }
            tablaAutores += "</table>";
        } catch (SQLException ex) {
            return "No existe usuario.";
        } finally {
            PoolConexion.getInstance().cerrarConexion(conex);
        }
        return tablaAutores;
    }
    
     public boolean Eliminar(String id){
  
    try{
        Conex = ConexionBD.getInstancia("websai","root","");
         if(!Conex.OpenCon())
            return false;
        }catch(ClassNotFoundException ex){
            return false;
        }  try {
           Conex.getSentencia().executeUpdate("delete from alumno where idAlumno="+id);
            
        } catch (SQLException ex) {
            return false;
        } 
       return true;
    }
     
     
     public boolean Buscar(String id){
    ResultSet consulta=null;
    try{
        Conex = ConexionBD.getInstancia("websai","root","");
         if(!Conex.OpenCon())
            return false;
        }catch(ClassNotFoundException ex){
            return false;
        }  try {
           consulta= Conex.getSentencia().executeQuery("select * from alumno where idAlumno= "+id);
            if(consulta.first()){
                this.id=id;
                this.nombre=consulta.getString(2);
                this.apmat=consulta.getString(3);
                this.appat=consulta.getString(4);
                this.matricula=consulta.getString(5);
                this.grupo=consulta.getString(6);
                this.proyecto=consulta.getString(7);
                
            }
        } catch (SQLException ex) {
            return false;
        } 
       return true;
    }
      @Override
    public String toString() {
        String xml="<?xml version='1.0' ?>";
        xml+="<alumno>"
                    +"<estado>OK</estado>"
                    +"<id>"+id+"</id>"
                    +"<nombre>"+nombre+"</nombre>"
                    +"<apellidoMaterno>"+apmat+"</apellidoMaterno>"    
                    +"<apellidoPaterno>"+appat+"</apellidoPaterno>"
                    +"<matricula>"+matricula+"</matricula>"
                    +"<grupo>"+grupo+"</grupo>"
                    +"<proyecto>"+proyecto+"</proyecto>"
                    +"</alumno>";
             return xml;
//        return "Servicio{" + "tipo=" + tipo + ", descripcion=" + descripcion + ", costo=" + costo + ", id=" + id + '}';
    }
      
      public static String ConsultarMats(){
         Connection conex = (Connection) PoolConexion.getInstance().getConexion();
         String selectMats = "<select id='cmbMatriculaAlumno'>";

         try {
             PoolConexion.getInstance().commit(conex);
             PreparedStatement stm = null;
             ResultSet rs = null;

             stm = (PreparedStatement) conex.prepareStatement(SqlAlumno.SELECT_ALL);
             rs = stm.executeQuery();

             while(rs.next()){
                 selectMats += "<option value='" + rs.getString(5) + "'>" + rs.getString(5) + "</option>";
             }
             selectMats += "</select>";
         } catch (SQLException ex) {
             return "Error " + ex;
         } finally {
             PoolConexion.getInstance().cerrarConexion(conex);
         }
         return selectMats;
     }
      
      public static String ConsultarCalifs(){
          Connection conex = (Connection) PoolConexion.getInstance().getConexion();
          String tablaCalifs;
          try {
            PoolConexion.getInstance().commit(conex);
            PreparedStatement stm = null;
            ResultSet rs = null;

            stm = (PreparedStatement) conex.prepareStatement(SqlCalificacion.SELECT_ALL);
            rs = stm.executeQuery();

            tablaCalifs = "<table align='center' border='1' color='red'>"
                + "<tr>"
                + "<th>Matricula</th>"
                + "<th>Rubros</th>"
                + "<th>Calificación</th>"
                + "<th>Materia</th>"
                + "</tr>";

            while (rs.next()) {
                tablaCalifs += "<tr>"
                        + "<td>" + rs.getString(2) + "</td>"
                        + "<td>" + rs.getString(3) + "</td>"
                        + "<td>" + rs.getString(4) + "</td>"
                        + "<td>" + rs.getString(5) + "</td>"
                        + "<td>"+"<button type='button' name='btnActualizarr'"
                        + "onclick='busquedaAlumno("+rs.getInt(1)+");'>Actualizar</button></td>"
                        + "<td>"+"<button type='button' name='btnEliminar'"
                        + "onclick='borrarAlumno("+rs.getInt(1)+");'>Eliminar</button></td>"
                        + "</tr>";
                        
            }
            tablaCalifs += "</table>";
        } catch (SQLException ex) {
            return "No existe usuario.";
        } finally {
            PoolConexion.getInstance().cerrarConexion(conex);
        }
        return tablaCalifs;
      }
}
